import 'package:dartkit/dartkit.dart';
import 'package:postgres/postgres.dart';

part 'model.dart';

class PomoTable extends TableForUser<Pomo> {
  @override
  final String tableName = "pomos";
  @override
  final Connection conn;
  @override
  Pomo unzip(Json raw) => Pomo.fromRaw(raw);

  PomoTable(this.conn);

  @override
  Future<void> createTable() async {
    // 好像是番茄的开始和结束时间不能冲突
    await conn.execute("""
      CREATE EXTENSION IF NOT EXISTS btree_gist;
""");

    // end是一个保留字，因此要加双引号
    // TIMESTAMP务必要带上时区，因为我所在的东八区以及一直以来用的DateTime都是带时区的
    await conn.execute("""
      CREATE TABLE IF NOT EXISTS $tableName (
        id SERIAL,
        user_id INT NOT NULL,
        begin TIMESTAMP WITH TIME ZONE NOT NULL,
        "end" TIMESTAMP WITH TIME ZONE NOT NULL,
        title VARCHAR(50) NOT NULL DEFAULT '',
        summary TEXT NOT NULL DEFAULT '',
        PRIMARY KEY (id, user_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        EXCLUDE USING GIST (
          user_id WITH =,
          tstzrange(begin, "end") WITH &&
        )
      );
    """);

    await conn.execute("""
      CREATE INDEX IF NOT EXISTS idx_${tableName}_begin ON $tableName (begin)
  """);

    await conn.execute("""
     CREATE INDEX IF NOT EXISTS idx_${tableName}_end ON $tableName ("end")
  """);
  }

  @override
  Future<Pomo> create(Json args) async {
    // 从参数中获取id、begin和end
    final int userId = args.getField<int>("user_id", "创建番茄需要传入user_id");
    final DateTime begin =
        args.getField<DateTime>("begin", "创建番茄需要传入开始时间begin");
    final DateTime end = args.getField<DateTime>("end", "创建番茄需要传入结束时间end");

    // 插入数据并返回新插入的 id
    final result = await conn.execute(Sql.named("""
      INSERT INTO $tableName (user_id, begin, "end")
      VALUES (@user_id, @begin, @end)
      RETURNING *;
    """), parameters: {
      'user_id': userId,
      'begin': begin,
      'end': end,
    });

    if (result.isEmpty) throw BugDev("创建番茄成功，但找不到番茄记录");

    // 获取插入后的第一行数据
    final row = result.first;
    return Pomo.fromRaw(row.toColumnMap());
  }

  @override
  Future<void> update(Pomo item) async {
    // 更新番茄记录
    final result = await conn.execute(Sql.named("""
      UPDATE $tableName
      SET begin = @begin, "end" = @end, title = @title, summary = @summary
      WHERE id = @id;
    """), parameters: {
      'id': item.id,
      'begin': item.period.begin,
      'end': item.period.end,
      'title': item.title,
      'summary': item.summary,
    });

    if (result.affectedRows == 0) throw BugDev("根据番茄id[${item.id}]找不到番茄来更新");
  }
}

extension PomoTableExtraSql on PomoTable {
  Future<List<Pomo>> findBetween({
    required Period period,
    required int userId,
  }) async {
    final results = await conn.execute(Sql.named("""
    SELECT *
    FROM $tableName
    WHERE user_id = @userId 
      AND begin >= @begin 
      AND "end" <= @end
    ORDER BY "end" DESC;
  """), parameters: {
      'userId': userId,
      'begin': period.begin,
      'end': period.end,
    });
    return results.map((row) => Pomo.fromRaw(row.toColumnMap())).toList();
  }
}

final PomoTable pomoTable = PomoTable(argConn.value);
